How VA Healthcare Data Systems Work: From MUMPS to SQL

The first thing to understand about Veterans Affairs healthcare data is that it was not born in tables. It was born in globals: compact, hierarchical, persistent structures in MUMPS [Massachusetts General Hospital Utility Multi-Programming System, a language and database environment designed for sparse clinical data], where the database is not a separate warehouse of nouns but part of the living nervous system of the application itself.

That single fact explains a remarkable amount.

To someone raised on Structured Query Language [SQL, the relational query language used to work with tables, rows, columns, joins, and schemas], a healthcare record looks as if it ought to be a collection of neat relations. One table for patients. One for visits. One for orders. One for medications. One for laboratory results. A respectable little municipal grid of data, all pavements, lampposts, and zoning laws. But Veterans Health Information Systems and Technology Architecture [VistA, the long-running Veterans Affairs clinical, administrative, and operational health information system] did not grow like a suburb planned from a brochure. It grew like an old city. It has alleys, courtyards, shortcuts, shrines, patched drains, magnificent public buildings, and a few doors that open only if you know the right person and the right command.

This is not an insult. It is an architectural description.

VistA’s historic strength came from proximity. Clinical applications, data structures, workflow logic, and user interaction lived close together. Computerized Patient Record System [CPRS, the graphical clinical front end used historically with VistA] did not simply display a passive database. It participated in a world where orders, notes, allergies, consults, medications, appointments, alerts, signatures, and local practices were braided into the same operational fabric. The data was not merely stored after the work happened. Often, the data was the work happening.

That is why the phrase “MUMPS to SQL” is both useful and dangerously small. It sounds like a plumbing job. Take water from this tank, pour it into that tank, keep the pressure steady, bill the customer. In reality, it is closer to translating a bazaar into a spreadsheet. A bazaar has stalls, debts, smells, habits, glances, and reputation. A spreadsheet has cells. Both can represent commerce. Only one remembers the vegetable seller’s nephew.

The technical center of gravity in classic VistA is VA FileMan [File Manager, the database management layer that defines, stores, secures, edits, and retrieves VistA data through data dictionaries]. FileMan gives MUMPS something like database governance: files, fields, records, cross-references, input transforms, pointers, templates, security, and data dictionaries. But these are not relational objects in the pure SQL sense. A FileMan file may resemble a table, a record may resemble a row, and a field may resemble a column, but the resemblance is cousinly, not identical. FileMan supports multiples, meaning nested subfiles inside records. SQL wants tables to stand separately and join through keys. FileMan can treat hierarchy as a natural inhabitant of the record.

This is where the first deep mismatch appears. SQL is very good at asking: “Which rows satisfy this condition?” FileMan is very good at supporting: “What does this clinical application need to know, prompt, validate, store, display, and cross-reference right now?” Those are not the same question.

MUMPS globals are sparse multidimensional arrays stored persistently. A global can look like a tree whose branches are subscripts and whose leaves are values. The classic notation, with its little caret prefix, is famously odd to newcomers: a name like ^DPT or ^DD does not look like a table name; it looks like a startled squirrel escaped from a typesetter’s drawer. But the idea is elegant. The structure can be deep where life is deep and empty where life is empty. Healthcare loves this. Most patients do not have most possible facts. A person may have no pacemaker, no chemotherapy, no allergy to penicillin, no spinal cord registry entry, no specialized prosthetics history, no active opioid agreement, no tuberculosis screening this year, and no reason at all to occupy a fat row with hundreds of nullable columns waiting like unused chairs in a government auditorium.

Sparse storage suited clinical reality before “schema flexibility” became a fashionable phrase in newer database systems.

FileMan adds meaning to these globals through the data dictionary. The data dictionary says what a file is, what fields exist, what values are allowed, what prompts should appear, what cross-references should fire, what pointers connect one file to another, and what transformations are applied at entry or display time. This is not just metadata in the decorative sense. It is executable institutional memory. It tells the system how to behave.

That is powerful. It is also the reason SQL extraction is difficult.

When a VistA site projects FileMan data into SQL, it is not merely copying bytes. It must infer relational shape from a hierarchical, application-coupled world. Files become tables. Multiples become child tables. Fields become columns. Pointers become foreign-key-like relationships. Cross-references become indexes or lookup logic. Internal values may become external display values. Dates, local codes, statuses, flags, packages, site-specific fields, and historical conventions must all pass through the narrow gate of relational representation.

Some of the loss is obvious. A nested multiple becomes a table. Fine. A pointer becomes a join. Fine. A coded field becomes a domain. Fine. But the more interesting loss happens in the meaning around the value.

A medication order in an operational system is not just a medication row. It is an instruction embedded in time, authority, pharmacy workflow, formulary rules, renewal practices, discontinuation logic, local configuration, package behavior, and clinical responsibility. A lab result is not merely a number. It has specimen context, collection time, result time, verification, units, reference ranges, abnormal flags, accessioning practices, local test names, mapped terminology, and the unpleasant little fact that different facilities may use similar words to mean different things.

When this becomes SQL, the receiving system may see cleaner shapes but thinner meaning.

That is the great trade. MUMPS and FileMan preserve operational intimacy. SQL improves population-scale visibility. The former knows how the clinic breathes. The latter knows how to count across the empire.

The Veterans Health Administration [VHA, the healthcare arm of the United States Department of Veterans Affairs] eventually needed enterprise-scale analytics, reporting, research, quality measurement, surveillance, and operations management. Local VistA systems could not remain isolated islands if the organization wanted national views of care. So data moved into the Corporate Data Warehouse [CDW, the enterprise data warehouse that consolidates VHA data into a logical model for reporting, analytics, research, and operations]. CDW is the great SQL-facing city downstream from many local operational rivers.

The flow, in simplified form, looks like this: clinical and administrative work happens in local VistA instances; data is stored in MUMPS globals structured and governed through FileMan and package logic; extraction processes pull selected data; transformations normalize, map, and reshape it; the warehouse stores it in relational structures; analysts, researchers, dashboards, registries, quality programs, and operational reports query it using SQL and related tools.

That sentence is tidy enough to be suspicious.

In production, every arrow in that sentence has teeth.

One tooth is latency. A clinician sees the operational record in the workflow system. An analyst sees data after extraction and loading. The difference may be minutes, hours, or longer depending on domain, pipeline, refresh cycle, validation, and downstream mart behavior. In clinical operations, latency is not a minor inconvenience. It changes truth. A patient who has an active order at noon may not look active in the warehouse yet. A discharge may be complete in one application and incompletely represented downstream. A consult may be administratively closed but clinically unresolved. Time is not a column; it is a predator.

Another tooth is provenance. VistA data is generated by people and systems doing work: nurses documenting administration, physicians signing notes, pharmacists verifying orders, clerks scheduling appointments, devices sending results, interfaces processing messages, batch jobs repairing or updating fields, and local staff building workarounds because the official workflow was designed by someone who has never stood near a clinic desk at closing time. By the time data arrives in SQL, it may have acquired a polished table name and a respectable schema, but its origin story may be blurred. Who asserted this? Under what authority? Was it entered directly, inferred, copied, corrected, imported, defaulted, or transformed? Without provenance, analytics becomes a well-dressed rumor.

A third tooth is semantic drift. Local VistA sites historically had local configuration, local naming, local build, and local operational habits. A field may be nationally defined, but its practical meaning can bend under local workflow. Stop codes, clinics, note titles, orderable items, consult services, laboratory tests, reminder terms, and health factors can carry local histories like old suitcases. SQL can faithfully move the suitcase. It cannot guarantee that every suitcase contains the same thing.

This is why representation failures are so often mislabeled as data quality failures.

A warehouse user sees missingness, duplicates, odd codes, conflicting dates, implausible statuses, or mismatched counts and says, with weary confidence, “The source data is dirty.” Sometimes it is. Healthcare has plenty of actual dirty data. But often the source data is not dirty in its native habitat. It is locally meaningful, workflow-coupled, temporally ambiguous, or structurally richer than the downstream model can express. The so-called dirt appears during translation.

A field can be perfectly adequate for driving a clinical screen and terrible for longitudinal analytics. A status can be obvious to a package routine and ambiguous to an external query. A date can mean entered date, event date, collection date, verification date, release date, signature date, or administrative processing date. A pointer can work beautifully inside FileMan but become semantically underpowered when flattened into a reporting table. A note title can support documentation workflow while failing as a clinical phenotype. None of that is “bad data” in the simple sense. It is data asked to testify in a court for which it was never prepared.

This is the non-obvious architectural insight: the path from MUMPS to SQL is not primarily a storage migration. It is a change in theory of knowledge.

MUMPS/FileMan asks: “How do we support this work safely and consistently at the point of care?” SQL warehousing asks: “How do we make this work legible across populations, time, facilities, and programs?” Both questions are legitimate. Neither is subordinate. Trouble begins when the second pretends it can answer itself by merely extracting from the first.

The operational system is full of early-binding decisions. A user selects a value from a prompt. An input transform accepts or rejects it. A package routine stores it in a certain location. A cross-reference fires. A local configuration decides what choices exist. A signature changes state. The meaning is bound close to workflow.

The warehouse often prefers late-binding interpretation. Analysts derive cohorts, join domains, map codes, group clinics, normalize concepts, and reinterpret events for measurement. That late binding is necessary because enterprise questions change. But it is dangerous when the binding logic is undocumented, duplicated, or hidden in private SQL scripts passed around like folk medicine. A research cohort definition that lives only in one analyst’s query is not governance. It is a spell.

The transport layer is another place where confusion breeds. Transport moves data. Meaning does not automatically come along in the same truck. Health Level Seven version two [HL7 v2, a widely used message standard for moving clinical events such as admissions, orders, and results] can carry patient updates or laboratory results from one system to another. Fast Healthcare Interoperability Resources [FHIR, a modern interoperability standard using modular resources such as Patient, Observation, MedicationRequest, and Encounter] can expose data through application programming interfaces. Extract, transform, and load [ETL, the pipeline pattern for moving data from sources into target systems] can populate warehouse tables. Open Database Connectivity [ODBC, a standard method for connecting applications to database systems] can allow SQL-facing tools to query mapped data. These mechanisms move representations. They do not, by themselves, settle whether two systems mean the same thing by “encounter,” “active medication,” “primary care visit,” “completed consult,” or “problem.”

Transport is the courier. Semantics is the passport office. In healthcare, the courier often arrives first and waits impatiently while the passport office discovers that the traveler has four names, three birth dates, two local identifiers, and a stamp from a kingdom that no longer exists.

The SQL Interface [SQLI, a VA FileMan mechanism for projecting FileMan files and fields through SQL and ODBC] recognized this translation problem explicitly. It mapped FileMan concepts into SQL equivalents: files or multiples toward tables, fields toward columns, records toward rows, field types toward domains. That mapping is useful, but it is not metaphysics. It gives relational access to a FileMan-shaped world. It does not magically make the world relational.

This distinction matters for architects because a naïve MUMPS-to-SQL strategy creates brittle certainty. It produces tables that look authoritative because tables always do. Rows have a soothing moral effect on technical people. They imply order. But a row can be a costume. Beneath it may be a nested clinical event, a local convention, a package-specific state machine, or a value that only makes sense when interpreted with a particular data dictionary version and workflow history.

The practical design implication is that every important data product needs an interpretation contract, not just a schema. A schema says what columns exist. An interpretation contract says what the row claims to represent, what source events qualify, what timestamps mean, what exclusions apply, what local mappings are accepted, how updates and reversals are handled, what provenance is preserved, what known ambiguities remain, and which use cases are unsafe.

This is especially important in clinical analytics and research. A phenotype derived from CDW is not merely a query. It is a clinical argument expressed as SQL. It should be versioned, reviewed, tested against chart samples where appropriate, and governed like a reusable clinical artifact. Otherwise the organization confuses query execution with knowledge production.

The move from VistA’s MUMPS world into SQL also exposes the difference between source of record and source of truth. Source of record means the system where an official transaction is stored. Source of truth means the system whose interpretation the organization accepts for a particular decision. These are not always identical. For medication dispensing, pharmacy package data may dominate. For patient demographics, identity systems may override local entries. For utilization, encounter and stop-code logic may be curated downstream. For quality reporting, the warehouse may become the practical source of truth even though it is not the operational source of record.

This can feel absurd until one remembers that healthcare organizations are not single organisms. They are federations of departments, incentives, regulations, budgets, histories, and professional tribes. The data architecture encodes that federalism. A patient does not have one “truth”; the patient has many operational representations, each produced for a purpose. Billing truth, clinical truth, scheduling truth, research truth, quality truth, and patient-facing truth overlap but do not collapse into one another.

In VHA, this becomes even more complex because of scale. A national integrated system has advantages that fragmented private healthcare systems can only gaze at wistfully, like a hungry student outside a sweet shop in north Calcutta. But integration at national scale also means that local nuance must be made enterprise-legible without being murdered in the process. That is a hard problem. It is not solved by buying a database, adopting an interoperability standard, or drawing a canonical model with handsome boxes.

Canonical models help. They create a shared target representation for concepts that appear across systems. But canonical models become dangerous when treated as royal decrees. A canonical “Encounter” can be useful only if it admits that outpatient visits, inpatient stays, telehealth contacts, community care events, administrative encounters, no-shows, cancellations, consult-linked visits, and workload capture may each have different lifecycle rules. A canonical model that erases workflow difference produces simplicity by laundering ambiguity.

The better architectural stance is humility with instrumentation. Preserve raw lineage where possible. Keep source identifiers. Keep local codes alongside mapped national concepts. Store both internal and external values when meaningful. Version terminology maps. Capture load timestamps separately from event timestamps. Separate operational replication from analytic curation. Build domain marts with clinical review, not only database engineering. Treat nulls as evidence requiring classification, not as blank holes. Maintain data dictionaries as living public infrastructure, not private archaeological equipment.

The role of terminology is equally unforgiving. Logical Observation Identifiers Names and Codes [LOINC, a standard terminology for laboratory and clinical observations], Systematized Nomenclature of Medicine Clinical Terms [SNOMED CT, a clinical terminology for problems, findings, procedures, and related concepts], RxNorm [a normalized medication terminology], International Classification of Diseases [ICD, a diagnosis classification system used heavily for billing, reporting, and epidemiology], and local VA terminologies all play parts. Mapping local concepts into standard vocabularies is not clerical tidying. It is semantic surgery. Sometimes the tissue matches. Sometimes it scars. Sometimes the local concept contains workflow meaning that the standard vocabulary was never designed to carry.

This is why MUMPS-to-SQL translation should be judged by use case. A financial workload report, a patient safety dashboard, a sepsis surveillance model, a hypertension registry, a pharmacovigilance study, and a clinical trial recruitment extract do not require the same truth. They may draw from similar tables, but their evidentiary standards differ. The warehouse should not pretend to be one big neutral lake from which all cups draw equally pure water. It is more like the Hooghly: navigable, useful, historically indispensable, and carrying many tributaries whose origins matter.

The current modernization context makes this lesson more urgent, not less. VA’s Federal Electronic Health Record [Federal EHR, the Oracle Health-based electronic health record shared across federal healthcare modernization efforts] changes the operational landscape, but it does not abolish the translation problem. Replacing or supplementing VistA shifts the source systems, workflows, configuration, and interfaces. It does not repeal semantic mismatch. If anything, coexistence periods make the problem sharper because data may come from legacy VistA, newer Federal EHR sites, national systems, community care feeds, registries, claims-adjacent sources, and migration layers at the same time.

During hybrid eras, architects must resist the pleasing lie that a single modern platform creates a single modern truth. Modern systems generate old ambiguities at higher resolution. They still have local configuration, workflow variation, interface queues, user burden, training gaps, safety patches, data conversion constraints, and reporting definitions that differ from operational documentation. The shiny train still runs on tracks laid through human institutions.

What remains architecturally defensible is not purity but disciplined translation.

A serious MUMPS-to-SQL architecture should preserve the distinction between replicated source data, conformed warehouse data, curated analytic domains, and published metrics. It should make transformation logic inspectable. It should document temporal semantics. It should expose provenance. It should treat data dictionary changes as events that can break downstream meaning. It should create feedback loops from analysts back to source stewards when representation failures appear. It should test measures against operational reality, not only against row counts. It should put clinical informaticists, integration engineers, database architects, and frontline workflow experts in the same room often enough to make everyone mildly uncomfortable and eventually more accurate.

The smallest useful mental model is this: VistA stores clinical work in a MUMPS/FileMan grammar; SQL warehouses translate that work into relational grammar for enterprise reasoning. The translation is necessary. It is also lossy. The job of the architect is not to deny the loss but to identify it, contain it, annotate it, and prevent downstream users from mistaking a convenient representation for the thing itself.

The map is not the territory, yes. But in healthcare IT, the map is also used to schedule surgery, pay claims, measure quality, detect harm, recruit studies, close gaps in care, and tell a veteran whether the system remembers him correctly. That gives the map moral weight. A bad map here does not merely confuse hikers. It can move work, money, attention, risk, and trust.

So the story of VA healthcare data systems is not the story of an old MUMPS database reluctantly becoming SQL. It is the story of a vast healthcare institution trying to convert lived clinical operations into enterprise knowledge without flattening away the very meaning that made the data useful in the first place. MUMPS gave VistA a dense operational memory. SQL gave VHA national analytic reach. The hard work lives in the crossing.